<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  

  
  <title>MySQL数据库操作（四）——多表之间的关系 | 海晨忆的博客</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  
  
  <meta name="description" content="主键与外键1、一张表可以有多个外键 2、外键是表的主键，可以是别的表，也可以是本身（少见） 3、外键可以为null，可以重复，但是不能不存在，必须要查的到 创建语法 foreign key(本表的列名) references 关联的表名(关联表的主键)">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL数据库操作（四）——多表之间的关系">
<meta property="og:url" content="https://haichenyi.com/2018/10/11/MySQL数据库操作（四）——多表之间的关系/index.html">
<meta property="og:site_name" content="海晨忆的博客">
<meta property="og:description" content="主键与外键1、一张表可以有多个外键 2、外键是表的主键，可以是别的表，也可以是本身（少见） 3、外键可以为null，可以重复，但是不能不存在，必须要查的到 创建语法 foreign key(本表的列名) references 关联的表名(关联表的主键)">
<meta property="og:locale" content="zh-Hans">
<meta property="og:updated_time" content="2018-10-11T15:03:14.674Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL数据库操作（四）——多表之间的关系">
<meta name="twitter:description" content="主键与外键1、一张表可以有多个外键 2、外键是表的主键，可以是别的表，也可以是本身（少见） 3、外键可以为null，可以重复，但是不能不存在，必须要查的到 创建语法 foreign key(本表的列名) references 关联的表名(关联表的主键)">
  
    <link rel="alternate" href="/atom.xml" title="海晨忆的博客" type="application/atom+xml">
  
  
    <link rel="icon" href="/uploads/artistic_image/head.jpg">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/css/style.css">
  <link rel="stylesheet" href="/css/highlight.css">
  <script>
  let antiquityStorage = window.sessionStorage.getItem('antiquitySessionStorage');
  if (antiquityStorage == '' || antiquityStorage == null) {
    var antiquityLoader = '<div id="loaderbox"><div class="loader"><div class="load-roll"><div class="load-top"></div><div class="load-right"></div><div class="load-bottom"></div></div></div></div>';
    document.write(antiquityLoader);
    document.body.style.overflow = 'hidden'
  }
  </script>
</head>

<body>
  <div id="fullpage" class="mobile-nav-right">
    
      <div id="wrapper" style="background-image: url(/uploads/artistic_image/bg.jpg)" title="背景图片来自网络">
    
    
      <header id="header">
  <div id="nav-toggle" class="nav-toggle"></div>
  <div class="head-box global-width">
    <nav class="nav-box nav-right">
      
        <a class="nav-item" href="/" title
        
        >首页</a>
      
        <a class="nav-item" href="/archives" title
        
        >归档</a>
      
        <a class="nav-item" href="/about" title
        
        >简历</a>
      
    </nav>
  </div>
</header>
      <div id="middlecontent" title class="global-width sidebar-left">
        <section id="main"><article id="post-MySQL数据库操作（四）——多表之间的关系" class="article global-container article-type-post" itemscope itemprop="blogPost">
  
    <header class="article-header">
      
  
    <h1 class="article-title" itemprop="name">
      MySQL数据库操作（四）——多表之间的关系
    </h1>
  

    </header>
  
  <div class="article-meta">
    <a href="/2018/10/11/MySQL数据库操作（四）——多表之间的关系/" class="article-date">
  <time datetime="2018-10-11T14:50:29.000Z" itemprop="datePublished">2018-10-11</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/数据库-MySQL/">数据库 -MySQL</a>
  </div>

    
  </div>
  
    <span id="busuanzi_container_page_pv">
      本文总阅读量<span id="busuanzi_value_page_pv"></span>次
    </span>
  

  <div class="article-inner">
    
    <div class="article-content article-content-cloud doorframe mac" itemprop="articleBody">
      
        <h3 id="主键与外键"><a href="#主键与外键" class="headerlink" title="主键与外键"></a>主键与外键</h3><p><code>1、一张表可以有多个外键</code></p>
<p><code>2、外键是表的主键，可以是别的表，也可以是本身（少见）</code></p>
<p><code>3、外键可以为null，可以重复，但是不能不存在，必须要查的到</code></p>
<p><strong>创建语法</strong> <code>foreign key(本表的列名) references 关联的表名(关联表的主键)</code></p>
<a id="more"></a>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line">//创建一个部门表</span><br><span class="line">CREATE TABLE dept(</span><br><span class="line">	did INT PRIMARY KEY AUTO_INCREMENT,</span><br><span class="line">	dname VARCHAR(50)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">//插入几条数据</span><br><span class="line">INSERT INTO dept(did,dname) VALUE(NULL,&apos;销售部&apos;);</span><br><span class="line">INSERT INTO dept(did,dname) VALUE(NULL,&apos;技术部&apos;);</span><br><span class="line">INSERT INTO dept(did,dname) VALUE(NULL,&apos;人力部&apos;);</span><br><span class="line">INSERT INTO dept(did,dname) VALUE(NULL,&apos;保洁部&apos;);</span><br><span class="line"></span><br><span class="line">//创建一个员工表，这里的的edid是一个外键，对应部门表的主键did</span><br><span class="line">CREATE TABLE employee(</span><br><span class="line">	eid INT PRIMARY KEY AUTO_INCREMENT,</span><br><span class="line">	ename VARCHAR(50),</span><br><span class="line">	edid INT,</span><br><span class="line">	CONSTRAINT fk_empl_dept FOREIGN KEY(edid) REFERENCES dept(did)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">//或者</span><br><span class="line">CREATE TABLE employee(</span><br><span class="line">	eid INT PRIMARY KEY AUTO_INCREMENT,</span><br><span class="line">	ename VARCHAR(50),</span><br><span class="line">	edid INT</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">alter table employee add CONSTRAINT fk_empl_dept FOREIGN KEY(edid) REFERENCES dept(did);</span><br><span class="line"></span><br><span class="line">//上面这样也可以设置外键</span><br></pre></td></tr></table></figure></div>
<h3 id="一对一关系-把从表的主键设置成外键与主表的主键关联"><a href="#一对一关系-把从表的主键设置成外键与主表的主键关联" class="headerlink" title="一对一关系 把从表的主键设置成外键与主表的主键关联"></a>一对一关系 <code>把从表的主键设置成外键与主表的主键关联</code></h3><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">create table emp(</span><br><span class="line">    eid int primary key auto_increment,</span><br><span class="line">    ename varchar(50)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">create table hmp(</span><br><span class="line">    hid int primary key auto_increment,</span><br><span class="line">    hname varchar(50),</span><br><span class="line">    constraint fk_hmp_emp foreign key(hid) references emp(eid)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">//如上，这样创建的两张表emp与hmp就是一对一的关系</span><br><span class="line"></span><br><span class="line">//插入数据</span><br><span class="line">insert into emp values(1,&apos;张三&apos;);</span><br><span class="line">insert into hmp values(1,&apos;李四&apos;);</span><br><span class="line">//这样张三跟李四就对应上了</span><br></pre></td></tr></table></figure></div>
<h3 id="多对多关系-两个主表-一个关联表"><a href="#多对多关系-两个主表-一个关联表" class="headerlink" title="多对多关系 两个主表+一个关联表"></a>多对多关系 <code>两个主表+一个关联表</code></h3><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br></pre></td><td class="code"><pre><span class="line">create table teacher(</span><br><span class="line">    tid int primary key auto_increment,</span><br><span class="line">    tname varchar(50)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">create table student(</span><br><span class="line">    sid int primary key auto_increment,</span><br><span class="line">    sname varchar(50)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">create table stu_tea(</span><br><span class="line">    tid int,</span><br><span class="line">    sid int,</span><br><span class="line">    constraint fk_student foreign key(sid) references student(sid),</span><br><span class="line">    constraint fk_teacher foreign key(tid) references teacher(tid)</span><br><span class="line">);</span><br><span class="line">//这里的stu_tea就是关联表。多对多并不是说多张表对应多张表</span><br><span class="line"></span><br><span class="line">//插入数据</span><br><span class="line">insert into teacher values(1,&apos;黄老师&apos;);</span><br><span class="line">insert into teacher values(2,&apos;李老师&apos;);</span><br><span class="line">insert into teacher values(3,&apos;赵老师&apos;);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">insert into student values(1,&apos;张三&apos;);</span><br><span class="line">insert into student values(2,&apos;李四&apos;);</span><br><span class="line">insert into student values(3,&apos;王五&apos;);</span><br><span class="line">insert into student values(4,&apos;赵六&apos;);</span><br><span class="line"></span><br><span class="line">//这里黄老师教过张三，李四</span><br><span class="line">insert into stu_tea values(1,1);</span><br><span class="line">insert into stu_tea values(1,2);</span><br><span class="line">//李老师教过王五，赵六</span><br><span class="line">insert into stu_tea values(2,3);</span><br><span class="line">insert into stu_tea values(2,4);</span><br><span class="line">//赵老师教过张三，李四，王五，赵六</span><br><span class="line">insert into stu_tea values(3,1);</span><br><span class="line">insert into stu_tea values(3,2);</span><br><span class="line">insert into stu_tea values(3,3);</span><br><span class="line">insert into stu_tea values(3,4);</span><br><span class="line"></span><br><span class="line">//上面这样，一个学生对应多个老师，一个老师也对应多个学生，叫做多对多的关系</span><br></pre></td></tr></table></figure></div>
<h3 id="一对多关系-一方不变，另一方在变化"><a href="#一对多关系-一方不变，另一方在变化" class="headerlink" title="一对多关系 一方不变，另一方在变化"></a>一对多关系 <code>一方不变，另一方在变化</code></h3><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">//我们一开始创建的员工表与部门表就是一对多</span><br><span class="line">create table dept(</span><br><span class="line">    did int primary key auto_increment,</span><br><span class="line">    dname varchar(50)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">create table emp(</span><br><span class="line">    eid int primary key auto_increment,</span><br><span class="line">    ename varchar(50)</span><br><span class="line">    edid int,</span><br><span class="line">    constraint fk_emp_dept foreign key(edid) references dept(did)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">//这里的一就是部门表（主表），多就是员工表（附表）</span><br><span class="line">//插入数据</span><br><span class="line">insert into dept values(10,&apos;技术部&apos;);</span><br><span class="line">insert into dept values(20,&apos;人事部&apos;);</span><br><span class="line"></span><br><span class="line">//张三，李四属于技术部，王五属于人事部</span><br><span class="line">insert into emp values(null,&apos;张三&apos;,10);</span><br><span class="line">insert into emp values(null,&apos;李四&apos;,10);</span><br><span class="line">insert into emp values(null,&apos;王五&apos;,20);</span><br><span class="line"></span><br><span class="line">//一个部门对应多个员工，一对多</span><br></pre></td></tr></table></figure></div>

      
    </div>
    
      <footer class="article-footer">
        完
      </footer>
    
  </div>
  
    
<nav id="article-nav">
  <div class="article-nav-block">
    
      <a href="/2018/10/11/MySQL数据库操作（五）——多表查询/" id="article-nav-newer" class="article-nav-link-wrap">
        <strong class="article-nav-caption"></strong>
        <div class="article-nav-title">
          
            MySQL数据库操作（五）——多表查询
          
        </div>
      </a>
    
  </div>
  <div class="article-nav-block">
    
      <a href="/2018/10/11/MySQL数据库操作（三）——数据库的备份与恢复/" id="article-nav-older" class="article-nav-link-wrap">
        <div class="article-nav-title">MySQL数据库操作（三）——数据库的备份与恢复</div>
        <strong class="article-nav-caption"></strong>
      </a>
    
  </div>
</nav>

    
  
  
</article>
</section>
        <aside id="sidebar">
  
    <div class="widget-box">
  <div class="avatar-box avatar-item">
    <img class="avatar" src="/uploads/artistic_image/head.jpg" title="头像来自网络"></img>
    <h3 class="avatar-name">
      
        海晨忆
      
    </h3>
    <p class="avatar-slogan">
      先谋生，再谋爱。人间值得，未来可期。
    </p>
  </div>
</div>


  
    
  <div class="widget-box">
    <h3 class="widget-title">分类</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/Android-Socket/">Android -Socket</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-加密算法/">Android -加密算法</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-常用功能/">Android -常用功能</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-框架/">Android -框架</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-源码解析/">Android -源码解析</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-自定义view/">Android -自定义view</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-设计模式/">Android -设计模式</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/JAVA-并发/">JAVA -并发</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Java-SpringBoot/">Java -SpringBoot</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Tomcat/">Tomcat</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/kotlin/">kotlin</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/数据库-JDBC/">数据库 -JDBC</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/数据库-MySQL/">数据库 -MySQL</a></li></ul>
    </div>
  </div>


  
    

  
    
  
    
  <div class="widget-box">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/07/">July 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/11/">November 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/10/">October 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/09/">September 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/08/">August 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/07/">July 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">June 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/10/">October 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/05/">May 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/03/">March 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/02/">February 2018</a></li></ul>
    </div>
  </div>

  
    
  <div class="widget-box">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2021/07/27/JAVA—公平锁，非公平锁，悲观锁，乐观锁，死锁/">JAVA—公平锁，非公平锁，悲观锁，乐观锁，死锁</a>
          </li>
        
          <li>
            <a href="/2021/07/19/JAVA—线程同步器AQS/">JAVA—线程同步器AQS</a>
          </li>
        
          <li>
            <a href="/2021/07/07/Android模拟点击/">Android模拟点击</a>
          </li>
        
          <li>
            <a href="/2019/11/27/SpringBoot系列-消息-RabbitMQ-（二十）/">SpringBoot系列-消息(RabbitMQ)（二十）</a>
          </li>
        
          <li>
            <a href="/2019/11/13/SpringBoot系列-Docker（十九）/">SpringBoot系列-Docker（十九）</a>
          </li>
        
      </ul>
    </div>
  </div>

  
      <div class="widget-box">
    <h3 class="widget-title">友情链接</h3>
    <div class="widget">
      
        <a class="hrf" style="display: block;" href="https://github.com/haichenyi" title target='_blank'
        >Github</a>
      
        <a class="hrf" style="display: block;" href="https://www.jianshu.com/u/6077ee440c37" title target='_blank'
        >简书</a>
      
        <a class="hrf" style="display: block;" href="https://blog.csdn.net/qq_27634797" title target='_blank'
        >CSDN</a>
      
    </div>
  </div>

  
</aside>
      </div>
      <footer id="footer">
  <div class="foot-box footers global-width">
    &copy;2017-2021 海晨忆 &nbsp;&nbsp;
    <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <span id="busuanzi_container_site_pv">阁下是第<span id="busuanzi_value_site_pv"></span>个访客</span>
  </div>
</footer>
      <script src="https://code.jquery.com/jquery-2.0.3.min.js"></script>
<script>
if (!window.jQuery) {
var script = document.createElement('script');
script.src = "/js/jquery-2.0.3.min.js";
document.body.write(script);
}
</script>

  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/script.js"></script>



    </div>
    <nav id="mobile-nav" class="mobile-nav-box">
  <div class="mobile-nav-img mobile-nav-top"></div>
  
    <a href="/" class="mobile-nav-link">首页</a>
  
    <a href="/archives" class="mobile-nav-link">归档</a>
  
    <a href="/about" class="mobile-nav-link">简历</a>
  
  <div class="mobile-nav-img  mobile-nav-bottom"></div>
</nav>    
  </div>
</body>
</html>